﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.IO;
using System.Data;
using FbKPI.Model;
using FbKPI.Facade;
using System.Transactions;

namespace FBKPI.Tools.ImportExcel
{
    public partial class ImportExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string script = "$(document).ready(function () { $('[id*=btn_import]').click(); });";
                ClientScript.RegisterStartupScript(this.GetType(), "load", script, true);

                btn_import.Visible = false;
                txt_mode.Value = Request.QueryString["mode"].ToString();
            }
        }

        protected void btn_upl_Click(object sender, EventArgs e)
        {
            if (upload.HasFile)
            {
                HttpPostedFile Files = upload.PostedFile;
                string ext = Path.GetExtension(Files.FileName);
                if (ext.Trim().ToLower()==".xls" || ext.Trim().ToLower()==".xlsx")// Files.ContentType == "application/vnd.ms-excel")
                {
                    int fileLen = Files.ContentLength;

                    byte[] FileData = new byte[fileLen];
                    Files.InputStream.Read(FileData, 0, fileLen);

                    if (txt_mode.Value == "employee")
                    {
                        string destPath = ConfigurationManager.AppSettings["EmployeeExcelUploadPath"].ToString();
                        string fileName = "EMP_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";

                        //destPath = destPath + "/" + fileName+"/";
                        if (!Directory.Exists(destPath))
                            Directory.CreateDirectory(destPath);

                        FileStream fs = new FileStream(destPath + fileName, FileMode.Create);
                        fs.Write(FileData, 0, FileData.Length);
                        fs.Close();

                        lbl_result.Text = upload.PostedFile.FileName + " has been successfully uploaded,please click button import !";

                        btn_import.Visible = true;
                        btn_upl.Visible = false;
                        upload.Enabled = false;
                        uploadpath.Value = destPath + fileName;
                    }
                    else if (txt_mode.Value == "kpi")
                    {
                        //string destPath = ConfigurationManager.AppSettings["KPIExcelUploadPath"].ToString();
                        string destPath = Server.MapPath("~/Upload/KPI/");
                        string fileName = "KPI_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";

                        if (!Directory.Exists(destPath))
                            Directory.CreateDirectory(destPath);

                        FileStream fs = new FileStream(destPath + fileName, FileMode.Create);
                        fs.Write(FileData, 0, FileData.Length);
                        fs.Close();

                        lbl_result.Text = upload.PostedFile.FileName + " has been successfully uploaded ,please click button import !";

                        btn_import.Visible = true;
                        btn_upl.Visible = false;
                        upload.Enabled = false;
                        uploadpath.Value = destPath + fileName;
                    }

                }
                else
                {
                    lbl_result.Text = "File type must be .xls or .xlsx";
                }
            }
            else 
            {
                lbl_result.Text = "Please select file to upload";
            }
        }

        protected void btn_import_Click(object sender, EventArgs e)
        {

            //Employee CurrUser = new EmployeeFacade().GetEmployeeByID(Convert.ToInt32(HttpContext.Current.Request.QueryString["User"]));

            System.Threading.Thread.Sleep(5000);
            sysLog objSysLog = new sysLog();
            sysLogFacade sysLogFacade = new sysLogFacade();
            if (uploadpath.Value != string.Empty)
            {
                ExcelReader.ExcelReader reader = new ExcelReader.ExcelReader();
                DataSet dsResult = null;
                string retVal = reader.Read(uploadpath.Value, out dsResult);

                string modul = txt_mode.Value;

                Employee CurrentUser = new EmployeeFacade().GetEmployeeByID(Request.QueryString["User"] == string.Empty ? 0 : Convert.ToInt32(Request.QueryString["User"]));

                if (isTemplateValid(dsResult, modul))
                {
                    if (retVal.Contains("success") && dsResult != null && CurrentUser != null)
                    {
                        #region Import Employee
                        if (modul == "employee")
                        {
                            List<Employee> employees = new List<Employee>();
                            //try
                            //{
                                for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
                                {
                                    if (dsResult.Tables[0].Rows[i][0].ToString().Trim() != "" && dsResult.Tables[0].Rows[i][1].ToString().Trim() != ""
                                        //&& dsResult.Tables[0].Rows[i][3].ToString().Trim() != "" && dsResult.Tables[0].Rows[i][4].ToString().Trim() != ""
                                        )
                                    {


                                        Employee emp = new Employee();
                                        emp.NIK = dsResult.Tables[0].Rows[i][0].ToString();
                                        emp.FirstName = dsResult.Tables[0].Rows[i][1].ToString();

                                        Division objDiv = new Division();
                                        if(dsResult.Tables[0].Rows[i][11].ToString().Trim().Length > 0)
                                        {
                                            objDiv = new DivisionFacade().GetDivisionByDescription(dsResult.Tables[0].Rows[i][11].ToString().Trim());
                                            if(objDiv==null)
                                            {
                                                objDiv = new Division();
                                                objDiv.Description = dsResult.Tables[0].Rows[i][11].ToString().Trim();
                                                objDiv.isActive=true;
                                                objDiv.CreateBy =CurrentUser.Email;
                                                objDiv.CreateDate = DateTime.Now;
                                                objDiv.BusinessGroupID = CurrentUser.BusinessGroupID;

                                                string msg = new DivisionFacade().Save(objDiv);
                                                if (msg.Trim().ToLower().Contains("success"))
                                                {
                                                    objDiv = new DivisionFacade().GetDivisionByDescription(dsResult.Tables[0].Rows[i][11].ToString().Trim());
                                                }


                                            }
                                        }

                                        Department objDept = new Department();
                                        if (dsResult.Tables[0].Rows[i][3].ToString().Trim().Length > 0)
                                        {
                                           objDept = new DepartmentFacade().GetDepartmentByDescription(dsResult.Tables[0].Rows[i][3].ToString().Trim());
                                           if (objDept != null)
                                           {
                                               emp.Department = objDept;
                                           }
                                           else
                                           {
                                               //emp.Department = null;
                                               objDept = new Department();
                                               objDept.Division = objDiv;
                                               objDept.Description = dsResult.Tables[0].Rows[i][3].ToString().Trim();
                                               objDept.CreateBy = CurrentUser.Email;
                                               objDept.CreateDate = DateTime.Now;
                                               objDept.isActive = true;
                                               objDept.BusinessGroupID = CurrentUser.BusinessGroupID;

                                               string msg = new DepartmentFacade().Save(objDept);
                                               if (msg.Trim().ToLower().Contains("success"))
                                               {
                                                   objDept = new DepartmentFacade().GetDepartmentByDescription(dsResult.Tables[0].Rows[i][3].ToString().Trim());
                                                   emp.Department = objDept;
                                               }
                                               else
                                                   emp.Department = null;

                                           }
                                        }

                                        Section objSection = new Section();
                                        if (dsResult.Tables[0].Rows[i][4].ToString().Trim().Length > 0)
                                        {
                                            objSection = new SectionFacade().GetSectionByDescription(dsResult.Tables[0].Rows[i][4].ToString().Trim());
                                            if (objSection != null)
                                            {
                                                emp.Section = objSection;
                                            }
                                            else
                                            {
                                                //emp.Section = null
                                                objSection = new Section();
                                                objSection.Description = dsResult.Tables[0].Rows[i][4].ToString().Trim();
                                                objSection.CreateBy = CurrentUser.Email;
                                                objSection.BusinessGroupID = CurrentUser.BusinessGroupID;
                                                objSection.Department = objDept;
                                                objSection.isActive = true;

                                                string msg = new SectionFacade().Save(objSection);

                                                if (msg.Trim().ToLower().Contains("success"))
                                                {
                                                    objSection = new SectionFacade().GetSectionByDescription(dsResult.Tables[0].Rows[i][4].ToString().Trim());
                                                    emp.Section = objSection;
                                                }
                                                else
                                                    emp.Section = null;

                                            }


                                        }

                                        SubSection objSubSection = new SubSection();
                                        if (dsResult.Tables[0].Rows[i][5].ToString().Trim().Length > 0)
                                        {
                                            objSubSection = new SubSectionFacade().GetSubSectionByDescription(dsResult.Tables[0].Rows[i][5].ToString().Trim());
                                            if (objSubSection != null)
                                            {
                                                emp.SubSection = objSubSection;
                                            }
                                            else
                                            {
                                                objSubSection = new SubSection();
                                                objSubSection.Description = dsResult.Tables[0].Rows[i][5].ToString().Trim();
                                                objSubSection.BusinessGroupID = CurrentUser.BusinessGroupID;
                                                objSubSection.CreateBy = CurrentUser.Email;
                                                objSubSection.isActive = true;
                                                objSubSection.Section = objSection;

                                                string msg = new SubSectionFacade().Save(objSubSection);
                                                if (msg.Trim().ToLower().Contains("success"))
                                                {
                                                    objSubSection = new SubSectionFacade().GetSubSectionByDescription(dsResult.Tables[0].Rows[i][5].ToString().Trim());
                                                    emp.SubSection = objSubSection;
                                                }
                                                else
                                                emp.SubSection = null;
                                            }
                                        }

                                        JobPosition objJobPosition = new JobPosition();
                                        if (dsResult.Tables[0].Rows[i][6].ToString().Trim().Length > 0)
                                        {
                                            objJobPosition = new JobPositionFacade().GetJobPositionByDescription(dsResult.Tables[0].Rows[i][6].ToString().Trim());
                                            if (objJobPosition != null)
                                            {
                                                emp.JobPosition = objJobPosition;
                                            }
                                            else
                                            {
                                                objJobPosition = new JobPosition();
                                                objJobPosition.Position = dsResult.Tables[0].Rows[i][6].ToString().Trim();
                                                objJobPosition.JobDescription = dsResult.Tables[0].Rows[i][6].ToString().Trim();
                                                objJobPosition.BusinessGroupID = CurrentUser.BusinessGroupID;
                                                objJobPosition.CreateBy = CurrentUser.Email;
                                                objJobPosition.isActive = true;

                                                string msg = new JobPositionFacade().Save(objJobPosition);

                                                if (msg.Trim().ToLower().Contains("success"))
                                                {
                                                    objJobPosition = new JobPositionFacade().GetJobPositionByDescription(dsResult.Tables[0].Rows[i][6].ToString().Trim());
                                                    emp.JobPosition = objJobPosition;
                                                }
                                                else
                                                emp.JobPosition = null;
                                            }
                                        }

                                        SubBranch objSubBranch = new SubBranch();

                                        if (dsResult.Tables[0].Rows[i][2].ToString().Trim().Length > 0)
                                        {
                                            //objDept = new DepartmentFacade().GetDepartmentByDescription(dsResult.Tables[0].Rows[i][3].ToString().Trim());
                                            objSubBranch = new SubBranchFacade().GetSubBranchByDescription(dsResult.Tables[0].Rows[i][2].ToString().Trim());
                                            if (objSubBranch != null)
                                            {
                                                emp.SubBranch = objSubBranch;
                                            }
                                            else
                                                emp.SubBranch = null;
                                        }



                                        emp.JobTitle = dsResult.Tables[0].Rows[i][7].ToString().Trim();
                                        emp.Email = dsResult.Tables[0].Rows[i][8].ToString().Trim();

                                        Employee objSuperior = new Employee();

                                        if (dsResult.Tables[0].Rows[i][9].ToString().Trim().Length > 0)
                                        {
                                            objSuperior = new EmployeeFacade().GetEmployeeByName(dsResult.Tables[0].Rows[i][9].ToString().Trim());
                                            if (objSuperior != null)
                                            {
                                                emp.Supervisor = objSuperior.ID;
                                            }
                                            else
                                                emp.Supervisor = 0;
                                        }

                                        Privilege objPrivilege = new Privilege();
                                        if (dsResult.Tables[0].Rows[i][10].ToString().Trim().Length > 0)
                                        {

                                            objPrivilege = new PrivilegeFacade().GetPrivilegeByDescription(dsResult.Tables[0].Rows[i][10].ToString().Trim());
                                            if (objPrivilege != null)
                                            {
                                                emp.Privilege = objPrivilege;
                                            }
                                            else
                                                emp.Privilege = null;
                                        }

                                        //emp.Privilege = null;
                                        emp.isActive = true;
                                        emp.CreateBy = CurrentUser.Email;
                                        employees.Add(emp);
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }


                                bool status = new EmployeeFacade().Save(employees);
                                retVal = status == true ? "Import Success" : "Import Failed, please contact IT Support";

                                objSysLog.Description ="Import Employee Berhasil";
                            //}
                            //catch (Exception ex)
                            //{
                            //    objSysLog.Description = ex.Message;
                            //    retVal = "Import Error : " + ex.Message;
                            //}


                           
                            objSysLog.LogEvent = "Import Employee";
                            objSysLog.Source = "ImportExcel.aspx.cs";
                            objSysLog.CreatedBy = CurrentUser.Email;
                            objSysLog.CreatedDate = DateTime.Now;
                            sysLogFacade.Save(objSysLog);

                        }

                        #endregion

                        #region Import KPI
                        else if (modul == "kpi")
                        {
                            List<KPI> kpis = new List<KPI>();
                            int retIntParseValue = 0;
                            //try
                            //{
                                for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
                                {
                                    if (dsResult.Tables[0].Rows[i][1].ToString().Trim() != "" && dsResult.Tables[0].Rows[i][2].ToString().Trim() != ""
                                        && dsResult.Tables[0].Rows[i][3].ToString().Trim() != "" && dsResult.Tables[0].Rows[i][4].ToString().Trim() != "")
                                    {
                                        KPI kpi = new KPI();
                                        kpi.Description = dsResult.Tables[0].Rows[i][1].ToString().Trim();


                                        GroupKPI objGroup = new GroupKPIFacade().GetGroupKPIByDescription(dsResult.Tables[0].Rows[i][2].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][2].ToString().Trim());
                                        if (objGroup == null)
                                        {
                                            objGroup = new GroupKPI();
                                            objGroup.Description = dsResult.Tables[0].Rows[i][2].ToString().Trim();
                                            objGroup.CreateBy = CurrentUser.Email;
                                            objGroup.isActive = true;

                                            string simpan = new GroupKPIFacade().Save(objGroup);
                                            objGroup = new GroupKPIFacade().GetGroupKPIByDescription(dsResult.Tables[0].Rows[i][2].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][2].ToString().Trim());
                                        }

                                        kpi.GroupKPI = objGroup;


                                        CategoryKPI objCategory = new CategoryKPIFacade().GetCategoryKPIByDescription(dsResult.Tables[0].Rows[i][3].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][3].ToString().Trim(), kpi.GroupKPI.ID);
                                        if (objCategory == null)
                                        {
                                            objCategory = new CategoryKPI();
                                            objCategory.Description = dsResult.Tables[0].Rows[i][3].ToString().Trim();
                                            objCategory.CreateBy = CurrentUser.Email;
                                            objCategory.GroupKPI = kpi.GroupKPI;
                                            objCategory.isActive = true;

                                            string simpan = new CategoryKPIFacade().Save(objCategory);
                                            objCategory = new CategoryKPIFacade().GetCategoryKPIByDescription(dsResult.Tables[0].Rows[i][3].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][3].ToString().Trim(), kpi.GroupKPI.ID);
                                        }
                                        kpi.CategoryKPI = objCategory;


                                        TypeKPI objType = new TypeKPIFacade().GetTypeKPIByDescription(dsResult.Tables[0].Rows[i][4].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][4].ToString().Trim(), kpi.CategoryKPI.ID);
                                        if (objType == null)
                                        {
                                            objType = new TypeKPI();
                                            objType.Description = dsResult.Tables[0].Rows[i][4].ToString().Trim();
                                            objType.CategoryKPI = kpi.CategoryKPI;
                                            objType.isActive = true;
                                            objType.CreateBy = CurrentUser.Email;
                                            objType.GroupKPI = kpi.GroupKPI;

                                            string simpan = new TypeKPIFacade().Save(objType);

                                            objType = new TypeKPIFacade().GetTypeKPIByDescription(dsResult.Tables[0].Rows[i][4].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][4].ToString().Trim(), kpi.CategoryKPI.ID);
                                        }
                                        kpi.TypeKPI = objType;

                                        SubTypeKPI objSubType = new SubTypeKPIFacade().GetSubTypeKPIByDescription(dsResult.Tables[0].Rows[i][5].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][5].ToString().Trim(), kpi.TypeKPI.ID);
                                        if (objSubType == null)
                                        {
                                            objSubType = new SubTypeKPI();
                                            objSubType.Description = dsResult.Tables[0].Rows[i][5].ToString().Trim();
                                            objSubType.GroupKPI = kpi.GroupKPI;
                                            objSubType.CategoryKPI = kpi.CategoryKPI;
                                            objSubType.TypeKPI = kpi.TypeKPI;
                                            objSubType.isActive = true;
                                            objSubType.CreateBy = CurrentUser.Email;

                                            string simpan = new SubTypeKPIFacade().Save(objSubType);
                                            objSubType = new SubTypeKPIFacade().GetSubTypeKPIByDescription(dsResult.Tables[0].Rows[i][5].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][5].ToString().Trim(), kpi.TypeKPI.ID);
                                        }
                                        kpi.SubTypeKPI = objSubType;

                                        kpi.Year = dsResult.Tables[0].Rows[i][6].ToString() == string.Empty ? 0 : Convert.ToInt16(dsResult.Tables[0].Rows[i][6].ToString().Trim());
                                        kpi.Periode = dsResult.Tables[0].Rows[i][7].ToString().Trim();
                                        kpi.Polarization = dsResult.Tables[0].Rows[i][8].ToString().Trim();

                                        UoM objUom = new UoMFacade().GetUoMByDescription(dsResult.Tables[0].Rows[i][9].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][9].ToString().Trim());
                                        if (objUom == null)
                                        {
                                            objUom = new UoM();
                                            objUom.Description = dsResult.Tables[0].Rows[i][9].ToString().Trim();
                                            objUom.isActive = true;
                                            objUom.CreateBy = CurrentUser.Email;
                                            if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "numeric")
                                                objUom.Format = UoMFormat.Numeric;
                                            else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "event")
                                                objUom.Format = UoMFormat.Event;
                                            else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "month")
                                                objUom.Format = UoMFormat.Month;
                                            else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "date")
                                                objUom.Format = UoMFormat.Date;
                                            else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "day")
                                                objUom.Format = UoMFormat.Day;

                                            string simpan = new UoMFacade().Save(objUom);
                                            objUom = new UoMFacade().GetUoMByDescription(dsResult.Tables[0].Rows[i][9].ToString() == string.Empty ? "" : dsResult.Tables[0].Rows[i][9].ToString().Trim());
                                        }
                                        kpi.UoM = objUom;

                                        
                                        UoMDetail objUomDetail = new UoMDetailFacade().GetUoMDetailByDescription(dsResult.Tables[0].Rows[i][11].ToString() == string.Empty ? "" : dsResult.Tables[0].Rows[i][11].ToString().Trim(), kpi.UoM == null ? 0 : kpi.UoM.ID);
                                        if (objUomDetail == null)
                                        {
                                            objUomDetail = new UoMDetail();
                                            objUomDetail.Description = dsResult.Tables[0].Rows[i][11].ToString().Trim();
                                            objUomDetail.UoM = kpi.UoM;

                                            //if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "numeric")
                                            //    objUomDetail.UoMType = "Numeric";
                                            //else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "event")
                                            //    objUomDetail.UoMType = "Event";
                                            //else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "month")
                                            //    objUomDetail.UoMType = "Month";

                                            objUomDetail.UoMType = dsResult.Tables[0].Rows[i][10].ToString().Trim();

                                            objUomDetail.isActive = true;
                                            objUomDetail.ListValue = dsResult.Tables[0].Rows[i][12].ToString().Trim();
                                            objUomDetail.CreateBy = CurrentUser.Email;

                                            string simpan = new UoMDetailFacade().Save(objUomDetail);
                                            objUomDetail = new UoMDetailFacade().GetUoMDetailByDescription(dsResult.Tables[0].Rows[i][11].ToString() == string.Empty ? "" : dsResult.Tables[0].Rows[i][11].ToString().Trim(), kpi.UoM == null ? 0 : kpi.UoM.ID);
                                        }
                                        kpi.UoMDetailMax = objUomDetail;

                                        UoMDetail objUomDetailMin = new UoMDetailFacade().GetUoMDetailByDescription(dsResult.Tables[0].Rows[i][13].ToString().Trim() == string.Empty ? "" : dsResult.Tables[0].Rows[i][13].ToString().Trim(), kpi.UoM == null ? 0 : kpi.UoM.ID);
                                        if (objUomDetailMin == null)
                                        {
                                            objUomDetailMin = new UoMDetail();
                                            objUomDetailMin.Description = dsResult.Tables[0].Rows[i][13].ToString().Trim();
                                            objUomDetailMin.UoM = kpi.UoM;

                                            //if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "numeric")
                                            //    objUomDetail.UoMType = "Numeric";
                                            //else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "event")
                                            //    objUomDetail.UoMType = "Event";
                                            //else if (dsResult.Tables[0].Rows[i][10].ToString().Trim().ToLower() == "month")
                                            //    objUomDetail.UoMType = "Month";

                                            objUomDetailMin.UoMType = dsResult.Tables[0].Rows[i][10].ToString().Trim();

                                            objUomDetailMin.isActive = true;
                                            objUomDetailMin.ListValue = dsResult.Tables[0].Rows[i][14].ToString().Trim();
                                            objUomDetailMin.CreateBy = CurrentUser.Email;

                                            string simpan = new UoMDetailFacade().Save(objUomDetailMin);
                                            objUomDetailMin = new UoMDetailFacade().GetUoMDetailByDescription(dsResult.Tables[0].Rows[i][13].ToString() == string.Empty ? "" : dsResult.Tables[0].Rows[i][13].ToString().Trim(), kpi.UoM == null ? 0 : kpi.UoM.ID);
                                        }
                                        kpi.UoMDetailMin = objUomDetailMin;


                                        kpi.Weight = dsResult.Tables[0].Rows[i][15].ToString() == string.Empty || int.TryParse(dsResult.Tables[0].Rows[i][15].ToString().Trim(), out retIntParseValue) == false ? 0 : Convert.ToInt32(dsResult.Tables[0].Rows[i][15].ToString());
                                        kpi.isActive = true;
                                        kpi.BusinessGroupID = CurrentUser.BusinessGroupID;// dsResult.Tables[0].Rows[i][16].ToString() == string.Empty || int.TryParse(dsResult.Tables[0].Rows[i][16].ToString(), out retIntParseValue) == false ? 1 : Convert.ToInt32(dsResult.Tables[0].Rows[i][16].ToString());
                                        kpi.CreateBy = CurrentUser.Email;
                                        //kpi.PercentageMax = dsResult.Tables[0].Rows[i][16].ToString() == string.Empty || int.TryParse(dsResult.Tables[0].Rows[i][16].ToString(), out retIntParseValue) == false ? 1 : Convert.ToInt32(dsResult.Tables[0].Rows[i][16].ToString()); ;
                                        //kpi.PercentageMin = 80;

                                        if (dsResult.Tables[0].Rows[i][16].ToString().Trim() != "")
                                            kpi.PercentageMax = (int) (Convert.ToDouble(dsResult.Tables[0].Rows[i][16].ToString().Trim()) * 100) / 100;

                                        if (dsResult.Tables[0].Rows[i][17].ToString().Trim() != "")
                                            kpi.PercentageMin = (int) (Convert.ToDouble(dsResult.Tables[0].Rows[i][17].ToString().Trim()) * 100) / 100;


                                        Employee objEmpVerifier = new Employee();

                                        if (dsResult.Tables[0].Rows[i][18].ToString().Trim().Length > 0)
                                        {
                                            objEmpVerifier = new EmployeeFacade().GetEmployeeByName(dsResult.Tables[0].Rows[i][18].ToString().Trim());
                                            if (objEmpVerifier != null)
                                            {
                                                kpi.EmployeeIDVerifier = objEmpVerifier.ID;
                                            }
                                            else
                                                kpi.EmployeeIDVerifier = 0;
                                        }
                                        else
                                            kpi.EmployeeIDVerifier = 0;



                                        kpis.Add(kpi);
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }

                        #endregion

                                bool status = new KPIFacade().Save(kpis);

                                retVal = status == true ? "Import Success" : "Import Failed, please contact IT Support";

                                objSysLog.Description = "Import KPI berhasil";
                            //}
                            //catch (Exception ex)
                            //{
                            //    objSysLog.Description = ex.Message;
                            //    retVal = "Import Error : "+ex.Message;
                            //}

                            objSysLog.LogEvent = "Import KPI";
                            objSysLog.Source = "ImportExcel.aspx.cs";
                            objSysLog.CreatedBy = CurrentUser.Email;
                            objSysLog.CreatedDate = DateTime.Now;
                            sysLogFacade.Save(objSysLog);

                            
                        }
                    }

                    lbl_result.Text = retVal;
                    upload.Enabled = true;
                    btn_import.Visible = false;
                    btn_upl.Visible = true;
                    uploadpath.Value = string.Empty;
                }
                else
                {
                    lbl_result.Text = "Please use correct template";
                    upload.Enabled = true;
                    btn_import.Visible = false;
                    btn_upl.Visible = true;
                    uploadpath.Value = string.Empty;
                }
            }
            else
            {
                lbl_result.Text = "Please select file to upload";
                upload.Enabled = true;
                btn_import.Visible = false ;
                btn_upl.Visible = true;
                uploadpath.Value = string.Empty;
            }
        }

        private bool isTemplateValid(DataSet dsResult, string mode)
        {
            bool retVal = true;

            switch (mode)
            {
                #region Employee
                case "employee" :
                    //if (dsResult.Tables[0].Columns.Count == 9)
                    //{
                        foreach (DataColumn col in dsResult.Tables[0].Columns)
                        {
                            
                            if (col.Ordinal == 0 && !col.ColumnName.ToLower().Contains("nik"))
                            {
                                retVal = false;
                                break;
                            }


                            if (col.Ordinal == 1 && !col.ColumnName.ToLower().Contains("employee name"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 2 && !col.ColumnName.ToLower().Contains("sub branch"))
                            {
                                retVal = false; 
                                break;
                            }

                            if (col.Ordinal == 3 && !col.ColumnName.ToLower().Contains("department"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 4 && !col.ColumnName.ToLower().Contains("section"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 5 && !col.ColumnName.ToLower().Contains("sub section"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 6 && !col.ColumnName.ToLower().Contains("job position"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 7 && !col.ColumnName.ToLower().Contains("job title"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 8 && !col.ColumnName.ToLower().Contains("email"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 9 && !col.ColumnName.ToLower().Contains("super ordinat"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 10 && !col.ColumnName.ToLower().Contains("privilege"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 11 && !col.ColumnName.ToLower().Contains("division"))
                            {
                                retVal = false;
                                break;
                            }
                         
                        }
                    //}
                    //else
                    //    retVal = false;

                    break;
                #endregion

                #region KPI
                case "kpi":
                    //if (dsResult.Tables[0].Columns.Count == 18)// 27)
                    //{
                        foreach (DataColumn col in dsResult.Tables[0].Columns)
                        {
                            if (col.Ordinal == 0 && col.ColumnName.ToLower() != "no")
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 1 && col.ColumnName.ToLower() != "description")
                            { 
                                retVal = false; 
                                break;
                            }

                            if (col.Ordinal == 2 && col.ColumnName.ToLower() != "group")
                            {
                                retVal = false;
                                break;
                            }
                            if (col.Ordinal == 3 && col.ColumnName.ToLower() != "category")
                            {
                                retVal = false;
                                break;
                            }
                            if (col.Ordinal == 4 && col.ColumnName.ToLower() != "type")
                            {
                                retVal = false; 
                                break;
                            }
                            if (col.Ordinal == 5 && col.ColumnName.ToLower() != "subtype")
                            {
                                retVal = false; 
                                break;
                            }
                            if (col.Ordinal == 6 && col.ColumnName.ToLower() != "year")
                            {
                                retVal = false; 
                                break;
                            }
                            if (col.Ordinal == 7 && col.ColumnName.ToLower() != "periode")
                            {
                                retVal = false; 
                                break;
                            }
                            if (col.Ordinal == 8 && col.ColumnName.ToLower() != "polarization")
                            {
                                retVal = false;
                                break;
                            }
                            if (col.Ordinal == 9 && col.ColumnName.ToLower() != "uom")
                            {
                                retVal = false;
                                break;
                            }
                            if (col.Ordinal == 10 && col.ColumnName.ToLower() != "uomtype")
                            {
                                retVal = false;
                                break;
                            }
                            if (col.Ordinal == 11 && col.ColumnName.ToLower() != "uommax")
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 12 && col.ColumnName.ToLower() != "uommaxvalue")
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 13 && col.ColumnName.ToLower() != "uommin")
                            {
                                retVal = false; 
                                break;
                            }

                            if (col.Ordinal == 14 && col.ColumnName.ToLower() != "uomminvalue")
                            {
                                retVal = false;
                                break;
                            }
                            
                            if (col.Ordinal == 15 && col.ColumnName.ToLower() != "weight")
                            {
                                retVal = false;
                                break;
                            }
                            
                            //if (col.Ordinal == 16 && col.ColumnName.ToLower() != "businessgroupid")
                            //{
                            //    retVal = false;
                            //    break;
                            //}

                            if (col.Ordinal == 16 && !col.ColumnName.ToLower().Contains("maxprosentase"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 17 && !col.ColumnName.ToLower().Contains("minprosentase"))
                            {
                                retVal = false;
                                break;
                            }

                            if (col.Ordinal == 18 && !col.ColumnName.ToLower().Contains("employee"))
                            {
                                retVal = false;
                                break;
                            }
                        }
                #endregion
                    //}
                    //else
                    //    retVal = false;

                    break;
                default:
                    break;
            }


            return retVal;
        }
    }
}